package cn.sugarscat.flight;

import cn.sugarscat.db.DB;

import java.sql.ResultSet;
import java.sql.SQLException;

public class SeatManager {
    public static boolean insertSeat(Seat seat) {
        DB db = new DB();
        boolean ok = false;
        int booked = seat.isBooked()?1:0;
        String sql = "INSERT INTO `seat`(" +
                "`flight_id`, " +
                "`seat_number`, " +
                "`seat_type`, " +
                "`seat_booked`) VALUES ('" +
                seat.getFlightId() + "', '" +
                seat.getNumber() + "', '" +
                seat.getType() + "', '" +
                booked +  "');";
        if(db.executeInsert(sql)>0)
            ok = true;
        db.closeDB();
        return ok;
    }

    public static void generateSeatingMap(int flightId, String type, int num) {
        boolean ok = false;
        for (int i = 1; i <= num; i++) {
            String number = type + i;
            Seat seat = new Seat(flightId, number, type, false);
            ok = insertSeat(seat);
        }
        if (!ok)
            System.out.println("部分座位生成失败，请检查系统！");
    }

    public static int querySeatId(int flightId, String area) {
        int seatId = -1;
        DB db = new DB();
        String sql = "SELECT `seat_id` FROM `seat` WHERE `seat_booked` = '0' " +
                "AND `flight_id` = ? AND `seat_type` = ? LIMIT 1";
        ResultSet res = db.executeQuery(sql, ""+flightId, area);
        try {
            if (res.next()) {
                seatId = res.getInt("seat_id");
            }
            res.close();
            db.closeDB();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return seatId;
    }

    public static boolean bookSeat(int seatId) {
        DB db = new DB();
        String sql = "UPDATE `seat` SET `seat_booked` = '1' WHERE `seat_id` = ?;";
        int rows = db.executeUpdate(sql, ""+seatId);
        db.closeDB();
        return rows>0;
    }

    public static void cancelBookSeat(int seatId) {
        DB db = new DB();
        String sql = "UPDATE `seat` SET `seat_booked` = '0' WHERE `seat_id` = ?;";
        db.executeUpdate(sql, ""+seatId);
        db.closeDB();
    }

    // 显示座位占用情况
    public static void printSeatOccupancy(int flightId) {
        DB db = new DB();
        String sql = """
                SELECT  s.seat_type AS seat_type,  COUNT(*) AS total_seats,
                  SUM(s.seat_booked) AS booked_seats,
                  (SUM(s.seat_booked) / COUNT(*)) * 100 AS occupancy_rate
                FROM
                  seat s
                GROUP BY
                  s.seat_type;""";
        ResultSet res = db.executeQuery(sql);
        try {
            System.out.println("航班ID： " + flightId);
            System.out.println("座位类型\t总座位数\t已预订座位数\t占用率");
            while (res.next()) {
                String seatType = res.getString("seat_type");
                int totalSeats = res.getInt("total_seats");
                int bookedSeats = res.getInt("booked_seats");
                double occupancyRate = res.getDouble("occupancy_rate");
                System.out.printf("%s\t\t%d\t\t%d\t\t%.2f%%\n", seatType, totalSeats, bookedSeats, occupancyRate);
            }
            res.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
